﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;

using SF.Utilities;

namespace SF.Import
{
    /// <summary>
    /// Excel文件导入帮助器
    /// </summary>
    public static class ExcelImportHelper
    {
        /// <summary>
        /// 从excel文件中获取数据并转换为指定的类型集合
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="excelPath">excel的完整路径</param>
        /// <returns>指定的类型的数据集合</returns>
        public static IEnumerable<T> GetExcelData<T>(string excelPath) where T : class,new()
        {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(BuildExcelConnectionString(excelPath));

            connection.Open();

            try
            {
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                DataTable sheetNames = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                List<string> sheetTableNames = new List<string>();
                for (int k = 0; k < sheetNames.Rows.Count; k++)
                {
                    sheetTableNames.Add(sheetNames.Rows[k]["TABLE_NAME"].ToString());
                }

                //从指定的表查询数据
                string sql = "select * from [" + sheetTableNames[0] + "]";
                OleDbDataAdapter cmd = new OleDbDataAdapter(sql, connection);

                DataSet ds = new DataSet();
                string tableName = GuidHelper.GetNewFormatGuid();
                cmd.Fill(ds, tableName);

                DataTable tempDt = ds.Tables[tableName].DefaultView.ToTable();

                DataTable dt = new DataTable();
                if (tempDt.Rows.Count == 0)
                { throw new Exception("excel 文件中不存在任何数据。"); }

                DataRow firstRow = tempDt.Rows[0];
                int colLen = tempDt.Columns.Count;
                for (int i = 0; i < colLen; i++)
                {
                    dt.Columns.Add(firstRow[i].ToString().Trim(), typeof(string));
                }
                for (int i = 1; i < tempDt.Rows.Count; i++)
                {
                    DataRow row = dt.NewRow();
                    for (int j = 0; j < colLen; j++)
                    {
                        row[j] = tempDt.Rows[i][j].ToString();
                    }
                    dt.Rows.Add(row);
                }

                return dt.ToList<T>(true);
            }
            catch (Exception e)
            {
                throw new Exception("将 excel 文件中的数据转换至 指定数据模型 的过程中出现异常，异常信息：" + e.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 根据excel的完整路径获取合适的数据连接字符串
        /// </summary>
        /// <param name="excelPath">excel的完整路径</param>
        /// <returns>数据连接字符串</returns>
        private static string BuildExcelConnectionString(string excelPath)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + "; " + "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
            if (excelPath.Substring(excelPath.LastIndexOf(".") + 1) == "xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + "; " + "Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
            }

            return connectionString;
        }
    }
}
